package lk.icta.core.dao.impl;


import lk.icta.common.AppConstants;
import lk.icta.common.dao.impl.BaseDAO;
import lk.icta.core.dao.LandRailwayDAO;
import lk.icta.core.entity.AlienationRailway;
import lk.icta.core.entity.LandRailway;
import lk.icta.user.entity.User;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import java.util.Date;
import java.util.EnumSet;
import java.util.List;

public class LandRailwayDAOImpl extends BaseDAO implements LandRailwayDAO {

    private static final Logger logger = LoggerFactory.getLogger(LandRailwayDAOImpl.class);

    @Transactional(propagation = Propagation.MANDATORY)
    public void addLand(LandRailway land, User user) {
        logger.debug("Adding a Land {} by {}", land.getLandFileNumber(), user.getUsername());
        land.getLifeCycleInfo().setCreatedUser(user);
        land.getLifeCycleInfo().setCreatedTimeStamp(new Date());
        land.getLifeCycleInfo().setLastUpdatedUser(user);
        land.getLifeCycleInfo().setLastUpdatedTimeStamp(new Date());
        em.persist(land);
    }

    @Transactional(propagation = Propagation.MANDATORY)
    public void updateLand(LandRailway land, User user) {
        logger.debug("Updating a Land {} by {}", land.getId(), user.getUsername());
        logger.debug("Updating a Land ------------------------------- version - {}", land.getVersion());
        land.getLifeCycleInfo().setLastUpdatedUser(user);
        land.getLifeCycleInfo().setLastUpdatedTimeStamp(new Date());
        em.merge(land);
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandRailway> getLandsByDistrictAndState(int districtId, LandRailway.State state, User user) {
        logger.debug("Loading land list by District Rail [District Id: {}] and State {}", districtId, state);
        Query q = em.createNamedQuery("getLandsByDistrictAndState");
        q.setParameter("districtId", districtId);
        q.setParameter("state", state);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
    public LandRailway getLandByIdUKey(long idUKey, User user) {
        logger.debug("Loading Land [Land Id: {}]", idUKey);
        return em.find(LandRailway.class, idUKey);
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<LandRailway> searchDetailedLandsOfDSDivisionBySearchText(String fileNo, String planNo,
                                                                         String lotNo, String holderName, User user, int pageNo) {
        logger.debug("Search Detailed  lands  by name {} user {} ", fileNo, user.getUsername());
        int noOfRows = AppConstants.MAX_ROWS_PER_PAGE;

        //Alienation State
        String stateQuery = " AND ( EXISTS (SELECT a FROM AlienationRailway a " +
                " WHERE l.id = a.land.id AND a.approveState in (:stateSet) ";

        //holder query needs checking aliention table avoid if text holder search text is empty
        /*String holderQuery = "( EXISTS (SELECT a FROM AlienationRailway a " +
                "WHERE l.id = a.land.id AND a.holder.name LIKE :holderName) )";*/

        String holderQuery = " AND a.holder.name LIKE :holderName ";


        //holderQuery = " AND " + holderQuery;
        if (holderName == null || holderName.trim().equals("")) {
            holderQuery = "";
        }

        stateQuery = stateQuery + holderQuery;

        stateQuery = stateQuery + " ) ) ";

        String planNoQuery = "l.planNumber LIKE :planNo AND ";
        if ("".equals(planNo.trim())) {
            planNoQuery = "(l.planNumber LIKE :planNo OR l.planNumber is null) AND ";
        }

        String fileNoQuery = "l.landFileNumber LIKE :fileNo AND ";
        if ("".equals(fileNo.trim())) {
            fileNoQuery = "(l.landFileNumber LIKE :fileNo OR l.landFileNumber is null) AND ";
        }

        String lotQuery = "l.lotNumber LIKE :lotNo  ";
        if ("".equals(lotNo.trim())) {
            lotQuery = "(l.lotNumber LIKE :lotNo OR l.lotNumber is null) ";
        }

        String query = "SELECT l FROM LandRailway l WHERE " +
                planNoQuery +
                fileNoQuery +
                lotQuery +
                stateQuery;

        // AND  a.land.district IN (:DSList) AND (a.land.branch.id =:branchId OR a.land.branch.id= 4)

        EnumSet<AlienationRailway.State> stateSet = null;
        if (user.getRole().getId().equals(AppConstants.SC_USER_ROLE)) {
            stateSet = EnumSet.of(AlienationRailway.State.DATA_ENTRY,
                    AlienationRailway.State.REJECTED_BY_BO);
        } else if (user.getRole().getId().equals(AppConstants.BO_USER_ROLE)) {
            stateSet = EnumSet.of(AlienationRailway.State.DATA_ENTRY,
                    AlienationRailway.State.REJECTED_BY_SH);
        } else if (user.getRole().getId().equals(AppConstants.SH_USER_ROLE)) {
            stateSet = EnumSet.of(AlienationRailway.State.APPROVED_BY_BO,
                    AlienationRailway.State.REJECTED_BY_SDH);
        } else if (user.getRole().getId().equals(AppConstants.SDH_USER_ROLE)) {
            stateSet = EnumSet.of(AlienationRailway.State.APPROVED_BY_SH,
                    AlienationRailway.State.REJECTED_BY_DH);
        } else if (user.getRole().getId().equals(AppConstants.DH_USER_ROLE)) {
            stateSet = EnumSet.of(AlienationRailway.State.APPROVED_BY_SDH);
        }


        if (user.getRole().getId().equals("SC")) {
            // AND (a.approveState in (:stateSet) AND  a.land.district IN (:DSList) AND (a.land.branch.id =:branchId) OR a.land.branch.id= 4)
            String userQuery = " AND (l.district IN (:DSList) AND (l.branch.id =:branchId) OR l.branch.id= 4) ";
            query = query + userQuery;
        } else if (user.getRole().getId().equals("BO") || user.getRole().getId().equals("SH")) {
            String userQuery = " AND (l.branch.id =:branchId) ";
            query = query + userQuery;
        }

        Query q = em.createQuery(query).setFirstResult((pageNo - 1) * noOfRows).setMaxResults(noOfRows);
        q.setParameter("fileNo", "%" + fileNo + "%");
        q.setParameter("planNo", "%" + planNo + "%");
        q.setParameter("lotNo", "%" + lotNo + "%");
        if (!holderQuery.equals("")) {
            q.setParameter("holderName", "%" + holderName + "%");
        }
        q.setParameter("stateSet", stateSet);

        if (user.getRole().getId().equals("SC")) {
            q.setParameter("DSList", user.getDistricts());
            q.setParameter("branchId", user.getBranch().getId());
        } else if (user.getRole().getId().equals("BO") || user.getRole().getId().equals("SH")) {
            q.setParameter("branchId", user.getBranch().getId());
        }

        //  populateDsDivisionList(q, user);
        List<LandRailway> l = q.getResultList();
        return q.getResultList();
    }

    @Override
    public List getLandByFileNumber(String fNo) {
        Query q = em.createNamedQuery("getLandByFileNo");
        q.setParameter("fNo", fNo);
        return q.getResultList();
    }

    @Override
    public List getLandByFileNumberByUser(String fNo, User user) {
        Query q = em.createNamedQuery("getLandByFileNoByUser");
        q.setParameter("fNo", fNo);
        populateDivisionList(q, user);
        return q.getResultList();
    }

    @Override
    public List getLandByFileNumberByUserAndState(String fNo, User user, EnumSet<LandRailway.State> stateSet) {
        Query q = em.createNamedQuery("getLandByFileNumberByUserAndState");
        q.setParameter("fNo", fNo);
        q.setParameter("stateSet", stateSet);
        populateDivisionList(q, user);
        return q.getResultList();
    }

    @Transactional(propagation = Propagation.NEVER, readOnly = true)
    public List<Object[]> getAllLandStatistics(int id, String searchBy, LandRailway.State landState, Date fromDate, Date toDate, LandRailway.MigrationState migState) {
        String query = "SELECT l.state AS landState, COUNT(l) AS count FROM LandRailway l";
        if (landState != null || searchBy != null || (fromDate != null && toDate != null) || migState != null) {
            boolean isAdded = false;
            query += " WHERE ";
            if (searchBy != null) {
                query += searchBy;
                isAdded = true;
            }

            if ((fromDate != null && toDate != null)) {
                query += isAdded ? " AND " : "";
                query += "l.lifeCycleInfo.lastUpdatedTimeStamp BETWEEN :start AND :end";
            }

            if (landState != null) {
                query += isAdded ? " AND " : "";
                query += "l.state = :landState";
            }

            if (migState != null) {
                query += isAdded ? " AND " : "";
                query += "l.migrationState = :migState";
            }

            /*if (migState > 0) {
                query += isAdded ? " AND " : "";
                if (migState == 1) {
                    query += "l.lifeCycleInfo.createdUser.username = 'admin'";
                } else {
                    query += "l.lifeCycleInfo.createdUser.username <> 'admin'";
                }
            }*/

        }
        query += " GROUP BY l.state";

        logger.debug("Dynamic Statistics Query  : {}", query);
        Query q = em.createQuery(query);

        if (searchBy != null) q.setParameter("divisionId", id);
        if ((fromDate != null && toDate != null)) {
            q.setParameter("start", fromDate);
            q.setParameter("end", toDate);
        }
        if (landState != null) q.setParameter("landState", landState);

        if (migState != null) q.setParameter("migState", migState);

        List<Object[]> result = q.getResultList();
        return result;
    }

    @Override
    public List<LandRailway> getLandStatisticReport(int branchId, int trackId, int districtId, Date fromDate, Date toDate, LandRailway.State landState) {
        String query = "SELECT l.state AS landState, COUNT(l) AS count FROM LandRailway l";
        if (landState != null || branchId > 0 || trackId > 0 || districtId > 0 || (fromDate != null && toDate != null)) {
            boolean isAdded = false;
            query += " WHERE ";
            if (branchId > 0) {
                query += "l.branch.id = :branchId";
                isAdded = true;
            }

            if (trackId > 0) {
                query += isAdded ? " AND " : "";
                query += "l.railwayTrack.id = :trackId";
                isAdded = true;
            }

            if (districtId > 0) {
                query += isAdded ? " AND " : "";
                query += "l.district.id = :districtId";
                isAdded = true;
            }

            if ((fromDate != null && toDate != null)) {
                query += isAdded ? " AND " : "";
                query += "l.lifeCycleInfo.lastUpdatedTimeStamp BETWEEN :start AND :end";
                isAdded = true;
            }

            if (landState != null) {
                query += isAdded ? " AND " : "";
                query += "l.state = :landState";
                isAdded = true;
            }

        }
        query += " GROUP BY l.state";

        logger.info("Dynamic Land Statistics Query  : {}", query);
        Query q = em.createQuery(query);

        if (branchId > 0) q.setParameter("branchId", branchId);
        if (trackId > 0) q.setParameter("trackId", trackId);
        if (districtId > 0) q.setParameter("districtId", districtId);
        if ((fromDate != null && toDate != null)) {
            q.setParameter("start", fromDate);
            q.setParameter("end", toDate);
        }
        if (landState != null) q.setParameter("landState", landState);

        List<LandRailway> l = q.getResultList();
        return l;
    }

    @Override
    public List<LandRailway> getLandDetailsList(int branchId, int trackId, int districtId, Date startDate, Date endDate, LandRailway.State landState) {
        String query = "SELECT a FROM LandRailway a";

        if (branchId != 0 || trackId != 0 || districtId != 0 || startDate != null || endDate != null || landState != null ) {
            boolean isAdded = false;
            query += " WHERE ";
            if (branchId != 0) {
                query += "a.branch.id =:branchId";
                isAdded = true;
            }

            if (trackId != 0) {
                query += isAdded ? " AND " : "";
                query += "a.railwayTrack.id = :trackId";
                isAdded = true;
            }

            if (districtId != 0) {
                query += isAdded ? " AND " : "";
                query += "a.district.id =:districtId";
                isAdded = true;
            }

            if(endDate != null && startDate != null){
                query += isAdded ? " AND " : "";
                query += "a.lifeCycleInfo.createdTimeStamp BETWEEN :startDate AND :endDate";
                isAdded = true;
            }else if(startDate == null){
                query += isAdded ? " AND " : "";
                query += "a.lifeCycleInfo.createdTimeStamp <=:endDate";
                isAdded = true;
            }

            if (landState != null) {
                query += isAdded ? " AND " : "";
                query += "a.state =:landState";
            }



        }

        Query q = em.createQuery(query);

        if (branchId != 0) q.setParameter("branchId", branchId);
        if (trackId != 0) q.setParameter("trackId", trackId);
        if (districtId != 0) q.setParameter("districtId", districtId);
        if(endDate != null) q.setParameter("endDate", endDate);
        if(startDate != null) q.setParameter("startDate", startDate);
        if(landState != null) q.setParameter("landState", landState);
        logger.debug("Dynamic Query  : {}", query);

        return q.getResultList();
    }

    @Override
    public List<LandRailway> getLandLikeFileNumber(String landFileNumber) {
        Query q = em.createNamedQuery("getLandFileNoLike");
        q.setParameter("fileNo", "%"+landFileNumber+"%");
        return q.getResultList();
    }

    private void populateDivisionList(Query q, User user) {

        if (user != null && user.getBranch() != null) {

            if (user.getDistricts() != null && user.getDistricts().size() > 0) {
                logger.debug("role : {}", user.getRole().getName());
                q.setParameter("ignoreDS", 0);
                q.setParameter("DSList", user.getDistricts());
                q.setParameter("branchId", user.getBranch().getId());
            } else {
                logger.debug("role  : {}", user.getRole().getName());
                q.setParameter("ignoreDS", 2);
                q.setParameter("DSList", null);
                q.setParameter("branchId", user.getBranch().getId());
            }


        } else {
            logger.debug("role  : {}", user.getRole().getName());
            q.setParameter("ignoreDS", 1);
            q.setParameter("DSList", null);
            q.setParameter("branchId", null);
        }

    }


}
